package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.JugyoinRelationBean;
import jp.bsws.bean.KeyValueBean;
import jp.bsws.bean.KeyValueListBean;
import jp.bsws.bean.KeyValueObjectBean;
import jp.bsws.common.BSWSConstants;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.JugyoinRelationModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class JugyoinRelationDao {
	private static JugyoinRelationDao ｊugyoinRelationDao = new JugyoinRelationDao();

	/** ログ */
	private Log log = LogFactory.getLog(JugyoinRelationDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static JugyoinRelationDao getInstance(){
		return ｊugyoinRelationDao;
	}

	private String SQL_JUGYOIN_RELATION = " SELECT * FROM T_JUGYOIN_RELATION WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている現金種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<JugyoinRelationModel> getJugyoinRelationInfo(Connection con, String pkGroupCod, Integer pkJugyoinSeq, int pkRelationKbn, String pkSeq) throws Exception{
		List<JugyoinRelationModel> resultList = new ArrayList<JugyoinRelationModel>();

		//検索条件の作成
		String whereInt = "";
		List<Integer> whereIntList = new ArrayList<Integer>();
		if( pkJugyoinSeq != null){
			whereInt += " AND JUGYOIN_SEQ = ? ";
			whereIntList.add(pkJugyoinSeq);
		}
		if( BSWSUtils.isNotZeroNull(pkRelationKbn)){
			whereInt += " AND RELATION_KBN = ? ";
			whereIntList.add(pkRelationKbn);
		}
		if( BSWSUtils.isNotBlankNull(pkSeq)){
			whereInt += " AND SEQ = ? ";
			whereIntList.add(Integer.parseInt(pkSeq));
		}

		PreparedStatement stmt = con.prepareStatement(SQL_JUGYOIN_RELATION + whereInt );
		stmt.setString(1, pkGroupCod);
		for(int i = 0; i < whereIntList.size(); i++){
			stmt.setInt(i + 2, (Integer)whereIntList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		JugyoinRelationModel jugyoinRelationModel = null;
		while(rs.next()){
			jugyoinRelationModel = new JugyoinRelationModel();
			jugyoinRelationModel.setStrGroupCod(rs.getString("GROUP_COD"));
			jugyoinRelationModel.setIntJugyoinSeq(rs.getInt("JUGYOIN_SEQ"));
			jugyoinRelationModel.setIntRelationKbn(rs.getInt("RELATION_KBN"));
			jugyoinRelationModel.setIntSeq(rs.getInt("SEQ"));
			jugyoinRelationModel.setStrAtai(rs.getString("ATAI"));
			resultList.add(jugyoinRelationModel);
		}
		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_JUGYOIN_RELATION_INSERT =
		" INSERT INTO " +
		"     T_JUGYOIN_RELATION ( " +
		"         GROUP_COD " +
		"        ,JUGYOIN_SEQ " +
		"        ,RELATION_KBN " +
		"        ,SEQ " +
		"        ,ATAI " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 店舗関連情報追加
	 */
	public int insertJugyoinRelationAll(Connection con, BSWSSession bSWSSession , Integer intSansyoJugyoinSeq, JugyoinRelationBean jugyoinRelationBean) throws Exception{

		int cnt = 0;
		//報酬種別情報登録
		List<KeyValueObjectBean> tmpList = jugyoinRelationBean.getHosyuShubetsuList();
		for(int i = 0; i < tmpList.size(); i++){
			KeyValueObjectBean kvo = tmpList.get(i);
			int seq = Integer.parseInt(kvo.getKey());
			String atai = kvo.getAtai();
			if(seq >= 0 && BSWSUtils.isNotBlankNull(atai)){
				cnt += insertJugyoinRelation(con, bSWSSession, intSansyoJugyoinSeq
						, BSWSConstants.KBN_HOSYU_SHUBETSU_WARIAI, seq, atai);
			}
		}

		return cnt;
	}

	/**
	 * 店舗関連情報追加
	 */
	public int insertJugyoinRelation(Connection con, BSWSSession bSWSSession, Integer intJugyoinSeq, int intRelationKbn, int intSeq, String strAtai) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_JUGYOIN_RELATION_INSERT);
		stmt.setString(1, bSWSSession.getStrGroupCod());
		stmt.setInt(2, intJugyoinSeq);
		stmt.setInt(3, intRelationKbn);
		stmt.setInt(4, intSeq);
		stmt.setString(5, strAtai);
		stmt.setString(6,  bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	/**
	 * 店舗関連情報削除
	 */
	public int deleteJugyoinRelation(Connection con, String pkGroupCod, Integer pkJugyoinSeq) throws Exception{

		PreparedStatement stmt = con.prepareStatement(" DELETE FROM  T_JUGYOIN_RELATION WHERE GROUP_COD = ? AND JUGYOIN_SEQ = ? ");

		stmt.setString(1, pkGroupCod);
		stmt.setInt(2, pkJugyoinSeq);

		int deleteCnt = stmt.executeUpdate();
		stmt.close();

		return deleteCnt;
	}

	private String SQL_J_H =
		" SELECT " +
		" 	J_H.GROUP_COD, " +
		" 	J_H.JUGYOIN_SEQ, " +
		" 	J_H.JUGYOIN_KNJ, " +
		" 	J_H.HS_SEQ, " +
		" 	J_H.HOSYU_SHUBETSU_KNJ, " +
		" 	IFNULL(JR.ATAI,J_H.WARIAI_NUM) WARIAI_NUM " +
		" FROM ( " +
		" 	SELECT J.GROUP_COD,J.JUGYOIN_SEQ,J.JUGYOIN_KNJ,H.HS_SEQ,H.HOSYU_SHUBETSU_KNJ,H.HOSYU_SHUBETSU_HYOJI_JUN, H.WARIAI_NUM " +
		" 	FROM " +
		" 	( " +
		" 		SELECT J.GROUP_COD,J.SEQ JUGYOIN_SEQ,J.JUGYOIN_KNJ FROM T_TENPO_RELATION TRJ " +
		" 		INNER JOIN M_JUGYOIN J ON ( " +
		" 			TRJ.GROUP_COD = J.GROUP_COD AND " +
		" 			TRJ.SEQ = J.SEQ AND " +
		" 			TRJ.GROUP_COD = ? AND " +
		" 			TRJ.TENPO_COD = ? AND " +
		" 			TRJ.RELATION_KBN = " + BSWSConstants.KBN_JUGYOIN +
		" 		) " +
		" 	) J, " +
		" 	( " +
		" 		SELECT HS.GROUP_COD,HS.SEQ HS_SEQ, HS.HOSYU_SHUBETSU_KNJ, HS.HOSYU_SHUBETSU_HYOJI_JUN, HS.WARIAI_NUM FROM T_TENPO_RELATION TRH " +
		" 		INNER JOIN M_HOSYU_SHUBETSU HS ON ( " +
		" 			TRH.GROUP_COD = HS.GROUP_COD AND " +
		" 			TRH.SEQ = HS.SEQ AND " +
		" 			TRH.GROUP_COD = ? AND " +
		" 			TRH.TENPO_COD = ? AND " +
		" 			TRH.RELATION_KBN = " + BSWSConstants.KBN_HOSYU_SHUBETSU +
		" 		) " +
		" 	) H " +
		" ) J_H " +
		" LEFT OUTER JOIN T_JUGYOIN_RELATION JR ON ( " +
		" 	J_H.GROUP_COD = JR.GROUP_COD AND " +
		" 	J_H.JUGYOIN_SEQ = JR.JUGYOIN_SEQ AND " +
		" 	J_H.HS_SEQ = JR.SEQ AND " +
		" 	JR.RELATION_KBN = " + BSWSConstants.KBN_HOSYU_SHUBETSU_WARIAI +
		" ) " +
		" ORDER BY J_H.JUGYOIN_SEQ, J_H.HOSYU_SHUBETSU_HYOJI_JUN ";

	/**
	 * 店舗に登録されている従業員の報酬種別の割合を取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueObjectBean> getJugyoinHosyuList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{

		List<KeyValueObjectBean> resultList = new ArrayList<KeyValueObjectBean>();

		PreparedStatement stmt = con.prepareStatement(SQL_J_H);
		stmt.setString(1, pkGroupCod);
		stmt.setString(2, pkTenpoCod);
		stmt.setString(3, pkGroupCod);
		stmt.setString(4, pkTenpoCod);

		ResultSet rs = stmt.executeQuery();

		int cnt = 0;
		int preJugyoinSeq = 0;
		while(rs.next()){
			KeyValueObjectBean kvoBean = new KeyValueObjectBean();
			int curJugyoinSeq = rs.getInt("JUGYOIN_SEQ");
			if(preJugyoinSeq != curJugyoinSeq){
				cnt = 0;
			}
			kvoBean.setKey(String.valueOf(curJugyoinSeq));
			kvoBean.setValue(String.valueOf(cnt));
			kvoBean.setAtai(rs.getString("WARIAI_NUM"));
			resultList.add(kvoBean);
			preJugyoinSeq = curJugyoinSeq;
			cnt++;
		}

		rs.close();
		stmt.close();

		return resultList;
	}
}